﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using PSI.Helper;
using PSI.Models.TMS;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;



namespace PSI.Services.TMS
{
    public class InConsignationService : PSIBaseService
    {

        public List<InConsignation> GetInConsignationListData(NameValueCollection formData)
        {
            var billNumber = formData["billNumber"];
            var dt1 = formData["dt1"];
            var dt2 = formData["dt2"];

            var result = new List<InConsignation>();

            var colList = new List<string> { "id", "f_bill_num", "f_vsl", "f_voy_num", "f_biz_man",
                "f_biz_dt", "f_arrival_dt", "f_clients", "f_status", "f_confirm_user_name",
                "f_confirm_dt", "f_plan_act_dt"};
            var indexId = colList.IndexOf("id");
            var indexBillNumber = colList.IndexOf("f_bill_num");
            var indexVsl = colList.IndexOf("f_vsl");
            var indexVoyNumber = colList.IndexOf("f_voy_num");
            var indexBizMan = colList.IndexOf("f_biz_man");
            var indexBizDT = colList.IndexOf("f_biz_dt");
            var indexArrivalDT = colList.IndexOf("f_arrival_dt");
            var indexClients = colList.IndexOf("f_clients");
            var indexStatus = colList.IndexOf("f_status");
            var indexConfirmUserName = colList.IndexOf("f_confirm_user_name");
            var indexConfirmDT = colList.IndexOf("f_confirm_dt");
            var indexPlanActDT = colList.IndexOf("f_plan_act_dt");

            var sb = new StringBuilder();

            sb.Append("select ").Append(colList.CombineWithComma());
            sb.Append(", stuff(( select distinct','+ c.f_cnt_owner from tms_in_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') cnt_owner,");
            sb.Append("stuff(( select distinct','+ c.f_cnt_type from tms_in_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') cnt_type,");
            sb.Append("stuff(( select distinct','+ c.f_goods from tms_in_consignation_detail c where c.parent_id=z.id  for xml path('')),1,1,'') f_goods,");
            sb.Append("(select COUNT(*) from tms_in_consignation_detail c where c.parent_id=z.id ) as cnt_num");
            sb.Append(" from tms_in_consignation z");
            var p = CreateDbParameters();
            if (string.IsNullOrEmpty(billNumber) && string.IsNullOrEmpty(dt1) && string.IsNullOrEmpty(dt2))
            {
                // 没有查询条件的时候，只显示最近三天的委托
                sb.Append(" where create_datetime between DATEADD(DAY, -3, GETDATE()) and GETDATE()");
            }
            else
            {
                sb.Append(" where (1 = 1) ");
                if (!string.IsNullOrEmpty(billNumber))
                {
                    // 按单号查询
                    sb.Append(" and (f_bill_num like @billNumber) ");
                    p.AddWithValue("billNumber", "%" + billNumber + "%");
                }

                if (!string.IsNullOrEmpty(dt1) || !string.IsNullOrEmpty(dt2))
                {
                    if (string.IsNullOrEmpty(dt1))
                    {
                        dt1 = dt2;
                    }
                    if (string.IsNullOrEmpty(dt2))
                    {
                        dt2 = dt1;
                    }
                    sb.Append(" and (convert(date, f_biz_dt) between @dt1 and @dt2)");
                    p.AddWithValue("dt1", dt1);
                    p.AddWithValue("dt2", dt2);
                }

            }
            sb.Append(" order by f_biz_dt desc, f_bill_num desc");

            var sql = sb.ToString();
            Query(sql, p, reader =>
            {
                var ic = new InConsignation
                {
                    Id = reader.GetString(indexId),
                    BillNumber = reader.GetString(indexBillNumber),
                    Vsl = reader.GetString(indexVsl),
                    BizMan = reader.GetString(indexBizMan),
                    Clients = reader.GetString(indexClients),
                    Status = reader.GetString(indexStatus),
                    Cnt = reader.GetInt32(reader.GetOrdinal("cnt_num")).ToString(),
                    Cnt_ower = reader.GetString(reader.GetOrdinal("cnt_owner")),
                    Cnt_type = reader.GetString(reader.GetOrdinal("cnt_type")),
                    goods = reader.GetString(reader.GetOrdinal("f_goods")),

                    ConfrimUserName = reader.GetString(indexConfirmUserName)
                };

                var voyNumber = reader.GetString(indexVoyNumber);
                if (!string.IsNullOrEmpty(voyNumber))
                {
                    ic.Vsl = ic.Vsl + "/" + voyNumber;
                }

                if (!reader.IsDBNull(indexBizDT))
                {
                    ic.BizDT = reader.GetDateTime(indexBizDT).ToYmd();
                }
                if (!reader.IsDBNull(indexArrivalDT))
                {
                    ic.DeliverDT = reader.GetDateTime(indexArrivalDT).ToYmdHm();
                }
                if (!reader.IsDBNull(indexConfirmDT))
                {
                    ic.ConfirmDT = reader.GetDateTime(indexConfirmDT).ToYmdHm();
                }
                if (!reader.IsDBNull(indexPlanActDT))
                {
                    ic.ActionDT = reader.GetDateTime(indexPlanActDT).ToYmd();
                }
                result.Add(ic);
            });

            // 最多返回100条记录
            if (result.Count > 100)
            {
                return (from item in result select item).Take(100).ToList();
            }
            else
            {
                return result;
            }
        }

        public List<InConsignationDetail> GetInConsignationDetailListData(string parentId)
        {
            var result = new List<InConsignationDetail>();

            var colList = new List<string> { "id","parent_id","f_bl_number", "f_cnt_type", "f_cnt_num", "f_address",
                "f_goods_owner", "f_goods_owner_tel", "f_memo", "f_cnt_owner", "f_unload_man",
                "f_unload_man_tel", "f_goods", "f_status","i_isweixin","f_plan_act_dt"};

            var indexId = colList.IndexOf("id");
            var indexBlNumber = colList.IndexOf("f_bl_number");
            var indexCntType = colList.IndexOf("f_cnt_type");
            var indexCntNumber = colList.IndexOf("f_cnt_num");
            var indexAddress = colList.IndexOf("f_address");
            var indexGoodsOwner = colList.IndexOf("f_goods_owner");
            var indexGoodsOwnerTel = colList.IndexOf("f_goods_owner_tel");
            var indexMemo = colList.IndexOf("f_memo");
            var indexCntOwner = colList.IndexOf("f_cnt_owner");
            var indexUnloadMan = colList.IndexOf("f_unload_man");
            var indexUnloadManTel = colList.IndexOf("f_unload_man_tel");
            var indexGoods = colList.IndexOf("f_goods");
            var indexStatus = colList.IndexOf("f_status");

            var where = parentId == "" ? "1=1" : "parent_id = @parentId";

            var sql = "select " + colList.CombineWithComma() +
                        @" from tms_in_consignation_detail
                        where " + where + " order by show_order";
            var p = CreateDbParameters();
            if (parentId != "")
            {
                p.AddWithValue("parentId", parentId);
            }
            Query(sql, p, reader =>
            {
                var icd = new InConsignationDetail
                {
                    Id = reader.GetString(indexId),
                    ParentId = reader.GetString(reader.GetOrdinal("parent_id")),
                    BlNumber = reader.GetString(indexBlNumber),
                    CntType = reader.GetString(indexCntType),
                    CntNumber = reader.GetString(indexCntNumber),
                    Address = reader.GetString(indexAddress),
                    GoodsOwner = reader.GetString(indexGoodsOwner),
                    GoodsOwnerTel = reader.GetString(indexGoodsOwnerTel),
                    Memo = reader.GetString(indexMemo),
                    CntOwner = reader.GetString(indexCntOwner),
                    UnloadMan = reader.GetString(indexUnloadMan),
                    UnloadManTel = reader.GetString(indexUnloadManTel),
                    PlanDt = reader.GetDateTime(reader.GetOrdinal("f_plan_act_dt")),
                    Goods = reader.GetString(indexGoods),
                    isweixin = (IsWXStatus)Enum.Parse(typeof(IsWXStatus), reader.GetInt32(reader.GetOrdinal("i_isweixin")).ToString()),
                    Status = reader.GetString(indexStatus)
                };
                result.Add(icd);
            });

            return result;
        }

        public Result EditInConsignation(NameValueCollection formData)
        {
            var id = formData["id"];
            var vsl = formData["vsl"];
            var voyNumber = formData["voyNumber"];
            var clients = formData["clients"];
            var deliverDate = formData["deliverDate"];
            var deliverTime = formData["deliverTime"];
            var bizMan = formData["bizMan"];
            var bizDT = formData["bizDT"];
            var userId = UserService.GetLoginUser().Id;

            if (string.IsNullOrEmpty(id))
            {
                // 新增
                WithTransaction(() =>
                {
                    var bns = new BillNumberService();
                    var mid = string.Format("{0:yyMMdd}", DateTime.Now);
                    var billNumber = bns.GetAutoNumber("进口委托", "JK", mid);
                    id = IdGeneratorService.NewId();

                    var sql = @"insert into tms_in_consignation(id, f_bill_num, f_vsl, f_voy_num, f_arrival_dt, f_clients,
                                    f_biz_man, f_biz_dt, create_user_id, create_datetime)
                                values(@id, @billNumber, @vsl, @voyNumber, @deliverDT, @clients, @bizMan, @bizDT, @userId, getdate())";

                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("billNumber", billNumber);
                    p.AddWithValue("vsl", vsl);
                    p.AddWithValue("voyNumber", voyNumber);
                    p.AddWithValue("deliverDT", deliverDate + " " + deliverTime);
                    p.AddWithValue("clients", clients);
                    p.AddWithValue("bizMan", bizMan);
                    p.AddWithValue("bizDT", bizDT);
                    p.AddWithValue("userId", userId);
                    Execute(sql, p);

                    new LogService().Log("新增进口委托，单号 = " + billNumber);
                });
            }
            else
            {
                // 编辑
                WithTransaction(() =>
                {
                    var sql = @"update tms_in_consignation
                                set f_vsl = @vsl, f_voy_num = @voyNumber, f_arrival_dt = @deliverDT, f_clients = @clients,
                                    f_biz_man = @bizMan, f_biz_dt = @bizDT,
                                    update_user_id = @userId, update_datetime = getdate()
                                where id = @id";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("vsl", vsl);
                    p.AddWithValue("voyNumber", voyNumber);
                    p.AddWithValue("deliverDT", deliverDate + " " + deliverTime);
                    p.AddWithValue("clients", clients);
                    p.AddWithValue("bizMan", bizMan);
                    p.AddWithValue("bizDT", bizDT);
                    p.AddWithValue("userId", userId);
                    Execute(sql, p);

                    new LogService().Log("编辑进口委托, 单号 = " + formData["billNumber"]);
                });
            }

            return new Result { Success = true, Id = id };
        }

        public InConsignation GetInConsignation(string id)
        {
            var result = new InConsignation { Id = id };

            var colList = new List<string> { "f_bill_num", "f_vsl", "f_voy_num", "f_clients",
            "f_arrival_dt", "f_biz_man", "f_biz_dt"};
            var indexBillNumber = colList.IndexOf("f_bill_num");
            var indexVsl = colList.IndexOf("f_vsl");
            var indexVoyNumber = colList.IndexOf("f_voy_num");
            var indexClients = colList.IndexOf("f_clients");
            var indexArrivalDT = colList.IndexOf("f_arrival_dt");
            var indexBizMan = colList.IndexOf("f_biz_man");
            var indexBizDT = colList.IndexOf("f_biz_dt");

            var sql = "select " + colList.CombineWithComma() + " from tms_in_consignation where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);

            Query(sql, p, reader =>
            {
                result.BillNumber = reader.GetString(indexBillNumber);
                result.Vsl = reader.GetString(indexVsl);
                result.VoyNumber = reader.GetString(indexVoyNumber);
                result.Clients = reader.GetString(indexClients);
                result.DeliverDate = reader.GetDateTime(indexArrivalDT).ToYmd();
                result.DeliverTime = reader.GetDateTime(indexArrivalDT).ToHm();
                result.BizMan = reader.GetString(indexBizMan);
                result.BizDT = reader.GetDateTime(indexBizDT).ToYmd();
            });

            return result;
        }

        public Result DeleteInConsignation(string idJson)
        {
            var idList = JsonConvert.DeserializeObject<List<string>>(idJson);

            // 检查委托是否能删除
            foreach (var id in idList)
            {
                var sql = "select f_status from tms_in_consignation where id = @id";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);

                var status = string.Empty;
                Query(sql, p, reader =>
                {
                    status = reader.GetString(0);
                });
                if (!string.IsNullOrEmpty(status))
                {
                    return new Result { Success = false, Messgae = "委托已经被确认运输，不能删除" };
                }
            }

            WithTransaction(() =>
            {
                foreach (var id in idList)
                {
                    var sql = @"
                            delete from tms_in_consignation_detail where parent_id = @id
                            delete from tms_in_consignation where id  = @id
                        ";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);

                    Execute(sql, p);

                    new LogService().Log("删除进口委托, id = " + id);
                }
            });

            return new Result { Success = true };
        }

        private string CombineCntNumber(string cntNumber1, string cntSeal1, string cntNumber2, string cntSeal2)
        {
            var sb = new StringBuilder();
            sb.Append(cntNumber1);
            if (!string.IsNullOrEmpty(cntSeal1))
            {
                sb.Append("/").Append(cntSeal1);
            }

            if (!string.IsNullOrEmpty(cntNumber2))
            {
                sb.Append(" ").Append(cntNumber2);
                if (!string.IsNullOrEmpty(cntSeal2))
                {
                    sb.Append("/").Append(cntSeal2);
                }
            }
            return sb.ToString();
        }

        private string CombineCntType(string cntType1, string cntType2)
        {
            if (string.IsNullOrEmpty(cntType1) && string.IsNullOrEmpty(cntType2))
            {
                return string.Empty;
            }

            if (string.IsNullOrEmpty(cntType1))
            {
                return cntType2 + "×1";
            }

            if (string.IsNullOrEmpty(cntType2))
            {
                return cntType1 + "×1";
            }

            if (cntType1 == cntType2)
            {
                return cntType1 + "×2";
            }
            else
            {
                return cntType1 + "×1 " + cntType2 + "×1";
            }
        }

        public Result EditInConsignationDetail(NameValueCollection formData)
        {
            var id = formData["id"];
            var parentId = formData["parentId"];
            var blNumber = formData["blNumber"];
            var cntNumber1 = formData["cntNumber1"];
            cntNumber1 = cntNumber1.ToUpper();
            var cntSeal1 = formData["cntSeal1"];
            var cntType1 = formData["cntType1"];
            var cntNumber2 = formData["cntNumber2"];
            cntNumber2 = cntNumber2.ToUpper();
            var cntSeal2 = formData["cntSeal2"];
            var cntType2 = formData["cntType2"];

            var cntOwner = formData["cntOwner"];

            var unloadMan = formData["unloadMan"];
            var unloadManTel = formData["unloadManTel"];

            var cntNum = this.CombineCntNumber(cntNumber1, cntSeal1, cntNumber2, cntSeal2);
            var cntType = this.CombineCntType(cntType1, cntType2);

            var address = formData["address"];
            var goods = formData["goods"];
            var goodsOwner = formData["goodsOwner"];
            var goodsOwnerTel = formData["goodsOwnerTel"];
            var memo = formData["memo"];

            var userId = UserService.GetLoginUser().Id;

            if (string.IsNullOrEmpty(id))
            {
                // 新增
                WithTransaction(() =>
                {
                    id = IdGeneratorService.NewId();

                    var sql = @"insert into tms_in_consignation_detail(id, parent_id, 
                        f_bl_number, f_cnt_num_1, f_cnt_seal_1, f_cnt_type_1,
                        f_cnt_num_2, f_cnt_seal_2, f_cnt_type_2, f_cnt_type, f_cnt_num,
                        f_address, f_goods_owner, f_goods_owner_tel, f_cnt_owner,
                        f_unload_man, f_unload_man_tel, f_goods,
                        f_memo, create_user_id, create_datetime)
                        values (@id, @parentId, @blNumber, @cntNum1, @cntSeal1, @cntType1,
                            @cntNum2, @cntSeal2, @cntType2, @cntType, @cntNum,
                            @address, @goodsOwner, @goodsOwnerTel, @cntOwner,
                            @unloadMan, @unloadManTel, @goods,
                            @memo, @userId, getdate())";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("parentId", parentId);
                    p.AddWithValue("blNumber", blNumber);
                    p.AddWithValue("cntNum1", cntNumber1);
                    p.AddWithValue("cntSeal1", cntSeal1);
                    p.AddWithValue("cntType1", cntType1);
                    p.AddWithValue("cntNum2", cntNumber2);
                    p.AddWithValue("cntSeal2", cntSeal2);
                    p.AddWithValue("cntType2", cntType2);
                    p.AddWithValue("cntType", cntType);
                    p.AddWithValue("cntNum", cntNum);
                    p.AddWithValue("address", address);
                    p.AddWithValue("goods", goods);
                    p.AddWithValue("goodsOwner", goodsOwner);
                    p.AddWithValue("goodsOwnerTel", goodsOwnerTel);
                    p.AddWithValue("unloadMan", unloadMan);
                    p.AddWithValue("unloadManTel", unloadManTel);
                    p.AddWithValue("cntOwner", cntOwner);
                    p.AddWithValue("memo", memo);
                    p.AddWithValue("userId", userId);

                    Execute(sql, p);

                });
            }
            else
            {
                // 编辑
                WithTransaction(() =>
                {
                    var sql = @"update tms_in_consignation_detail
                                set f_bl_number = @blNumber, f_cnt_num_1 = @cntNum1,
                                    f_cnt_seal_1 = @cntSeal1, f_cnt_type_1 = @cntType1,
                                    f_cnt_num_2 = @cntNum2, f_cnt_seal_2 = @cntSeal2,
                                    f_cnt_type_2 = @cntType2, f_cnt_num = @cntNum,
                                    f_cnt_type = @cntType, f_address = @address,
                                    f_goods_owner = @goodsOwner, f_goods_owner_tel = @goodsOwnerTel,
                                    f_cnt_owner = @cntOwner, f_unload_man = @unloadMan, f_unload_man_tel = @unloadManTel,
                                    f_goods = @goods,
                                    f_memo = @memo, update_user_id = @userId, update_datetime = getdate()
                                where id = @id";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("blNumber", blNumber);
                    p.AddWithValue("cntNum1", cntNumber1);
                    p.AddWithValue("cntSeal1", cntSeal1);
                    p.AddWithValue("cntType1", cntType1);
                    p.AddWithValue("cntNum2", cntNumber2);
                    p.AddWithValue("cntSeal2", cntSeal2);
                    p.AddWithValue("cntType2", cntType2);
                    p.AddWithValue("cntType", cntType);
                    p.AddWithValue("cntNum", cntNum);
                    p.AddWithValue("address", address);
                    p.AddWithValue("goods", goods);
                    p.AddWithValue("goodsOwner", goodsOwner);
                    p.AddWithValue("goodsOwnerTel", goodsOwnerTel);
                    p.AddWithValue("unloadMan", unloadMan);
                    p.AddWithValue("unloadManTel", unloadManTel);
                    p.AddWithValue("cntOwner", cntOwner);
                    p.AddWithValue("memo", memo);
                    p.AddWithValue("userId", userId);

                    Execute(sql, p);

                });
            }

            return new Result { Success = true };
        }

        public Result CheckCntNumber(string cntNumber1, string cntNumber2)
        {
            var ch = new ContainerHelper();

            var list = new List<string>();

            if (!ch.ContainerNumberIsValide(cntNumber1))
            {
                list.Add(cntNumber1);

            }
            if (!string.IsNullOrEmpty(cntNumber2))
            {
                if (!ch.ContainerNumberIsValide(cntNumber2))
                {
                    list.Add(cntNumber2);
                }
            }
            if (list.Count > 0)
            {
                var sb = new StringBuilder();
                sb.Append("箱号[ ");
                foreach (var num in list)
                {
                    sb.Append(num).Append(" ");
                }
                sb.Append("]不符合箱号规则，请确认是否仍要保存数据?");

                return new Result { Success = false, Messgae = sb.ToString() };
            }
            else
            {
                return new Result { Success = true };
            }
        }

        public InConsignationDetail GetInConsignationDetail(string id)
        {
            var result = new InConsignationDetail
            {
                Id = id
            };

            var colList = new List<string> { "f_bl_number", "f_cnt_num_1", "f_cnt_seal_1", "f_cnt_type_1",
            "f_cnt_num_2", "f_cnt_seal_2", "f_cnt_type_2", "f_address", "f_goods_owner","f_status", 
            "f_goods_owner_tel", "f_memo", "f_cnt_owner", "f_unload_man", "f_unload_man_tel", "f_goods","i_isweixin"};
            var indexBlNumber = colList.IndexOf("f_bl_number");
            var indexCntNumber1 = colList.IndexOf("f_cnt_num_1");
            var indexCntSeal1 = colList.IndexOf("f_cnt_seal_1");
            var indexCntType1 = colList.IndexOf("f_cnt_type_1");
            var indexCntNumber2 = colList.IndexOf("f_cnt_num_2");
            var indexCntSeal2 = colList.IndexOf("f_cnt_seal_2");
            var indexCntType2 = colList.IndexOf("f_cnt_type_2");
            var indexAddress = colList.IndexOf("f_address");
            var indexGoodsOwner = colList.IndexOf("f_goods_owner");
            var indexGoodsOwnerTel = colList.IndexOf("f_goods_owner_tel");
            var indexMemo = colList.IndexOf("f_memo");
            var indexCntOwner = colList.IndexOf("f_cnt_owner");
            var indexUnloadMan = colList.IndexOf("f_unload_man");
            var indexUnloadManTel = colList.IndexOf("f_unload_man_tel");
            var indexstatus = colList.IndexOf("f_status");
            var indexGoods = colList.IndexOf("f_goods");
            var isweixin = colList.IndexOf("i_isweixin");

            var sql = "select " + colList.CombineWithComma() + " from tms_in_consignation_detail where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            Query(sql, p, reader =>
            {
                result.BlNumber = reader.GetString(indexBlNumber);
                result.CntNumber1 = reader.GetString(indexCntNumber1);
                result.CntSeal1 = reader.GetString(indexCntSeal1);
                result.CntType1 = reader.GetString(indexCntType1);
                result.CntNumber2 = reader.GetString(indexCntNumber2);
                result.CntSeal2 = reader.GetString(indexCntSeal2);
                result.CntType2 = reader.GetString(indexCntType2);
                result.Address = reader.GetString(indexAddress);
                result.GoodsOwner = reader.GetString(indexGoodsOwner);
                result.GoodsOwnerTel = reader.GetString(indexGoodsOwnerTel);
                result.Memo = reader.GetString(indexMemo);
                result.CntOwner = reader.GetString(indexCntOwner);
                result.UnloadMan = reader.GetString(indexUnloadMan);
                result.UnloadManTel = reader.GetString(indexUnloadManTel);

                result.Status = reader.GetString(indexstatus);

                result.Goods = reader.GetString(indexGoods);

                result.isweixin =(IsWXStatus)Enum.Parse(typeof(IsWXStatus), reader.GetInt32(isweixin).ToString());
            });

            return result;
        }

        public Result DeleteInConsignationDetail(string idJson, string parentId)
        {
            var idList = JsonConvert.DeserializeObject<List<string>>(idJson);

            //检查能否删除
            foreach (var id in idList)
            {
                var sql = "select f_status from tms_in_consignation_detail where id = @id";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);
                var status = string.Empty;
                Query(sql, p, reader =>
                {
                    status = reader.GetString(0);
                });
                if (!string.IsNullOrEmpty(status) && status != "待下达计划")
                {
                    return new Result { Success = false, Messgae = "送货明细状态为[" + status + "]，不能删除" };
                }
            }

            WithTransaction(() =>
            {
                foreach (var id in idList)
                {
                    var sql = @"
                            delete from tms_in_consignation_detail where id = @id
                        ";
                    var p = CreateDbParameters();
                    p.AddWithValue("id", id);

                    Execute(sql, p);
                }

            });

            return new Result { Success = true };
        }

        public Result MakePlan(string id, string actionDate, string isweixin, string dispatcher)
        {
            var userId = UserService.GetLoginUser().Id;
            var user = new UserService().GetUser(userId);

            WithTransaction(() =>
            {
                var sql = @"update tms_in_consignation
                            set f_status = '已确认运输', f_confirm_user_name = @userName,
                                f_confirm_dt = getdate(), f_plan_act_dt = @actionDate,i_isweixin=@isweixin 
                            where id = @id

                            update tms_in_consignation_detail
                            set f_status = '待下达计划', f_plan_act_dt = @actionDate,i_isweixin=@isweixin
                            where parent_id = @id
                            ";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);
                p.AddWithValue("userName", user.Name);
                p.AddWithValue("actionDate", actionDate);
                p.AddWithValue("isweixin", string.IsNullOrEmpty(isweixin) ? IsWXStatus.notrelease : IsWXStatus.release);
                Execute(sql, p);

                if (!string.IsNullOrEmpty(dispatcher))
                {
                    var sqls = @"update tms_in_consignation_detail
                                set f_dispatcher = @dispatcher, f_plan_dt = getdate(), f_plan_num = @planNumber,
                                    f_plan_flag = 0, f_status = '待派车'
                                where parent_id = @id and f_plan_dt is null
                            ";
                    var pp = CreateDbParameters();
                    var planNumber = new InPlanService().GetAutoNumber();

                    pp.AddWithValue("id", id);
                    pp.AddWithValue("dispatcher", dispatcher);
                    pp.AddWithValue("planNumber", planNumber);
                    Execute(sqls, pp);
                }

                new LogService().Log("对进口委托确认运输，id = " + id);
            });


            return new Result { Success = true };
        }

        public string Upload(string fileName)
        {
            var workbook = WorkbookFactory.Create(new FileStream(fileName, FileMode.Open, FileAccess.Read));
          
            var sheet = workbook.GetSheetAt(0);
            var rowCount = sheet.PhysicalNumberOfRows;
            var colCount = sheet.GetRow(0).PhysicalNumberOfCells;

            #region 确认各列对应的数据
            var indexBizDT = -1;
            var indexClients = -1;
            var indexArrivalDT = -1;
            var indexBL = -1;
            var indexInOrOut = -1;
            var index20 = -1;
            var index40 = -1;
            var index40HQ = -1;
            var index40RF = -1;
            var indexGoods = -1;
            var indexCntNumber = -1;
            var indexAddress = -1;
            var indexUnloadMan = -1;
            var indexCntOwner = -1;
            var indexVsl = -1;
            var indexVoyNumber = -1;
            var indexUnloadManMobileTel = -1;
            var indexUnloadManTel = -1;
            var indexOhBillNumber = -1; //工作单号
            var indexBizMan = -1;
            var indexGoodsOwner = -1;

            var indexTruckCompany = -1;
            var indexTruckNumber = -1;
            var indexDriver = -1;
            var indexDriverTel = -1;

            var indexmeno = -1;  //备注2 装货时间
            for (int col = 0; col < colCount; col++)
            {
                var cell = sheet.GetRow(0).GetCell(col);
                if (cell == null) continue;

                var colTitle = cell.ToString();
                if (colTitle == "制单日期")
                {
                    indexBizDT = col;
                }
                if (colTitle == "托运人")
                {
                    indexClients = col;
                }
                if (colTitle == "装卸日期")
                {
                    indexArrivalDT = col;
                }
                if (colTitle == "运单号")
                {
                    indexBL = col;
                }
                if (colTitle == "装拆箱")
                {
                    indexInOrOut = col;
                }
                if (colTitle == "20'")
                {
                    index20 = col;
                }
                if (colTitle == "40'")
                {
                    index40 = col;
                }
                if (colTitle == "40HQ")
                {
                    index40HQ = col;
                }
                if (colTitle == "40RF")
                {
                    index40RF = col;
                }
                if (colTitle == "货名")
                {
                    indexGoods = col;
                }
                if (colTitle == "柜号")
                {
                    indexCntNumber = col;
                }
                if (colTitle == "卸货地点")
                {
                    indexAddress = col;
                }
                if (colTitle == "卸货联系人")
                {
                    indexUnloadMan = col;
                }
                if (colTitle == "船公司")
                {
                    indexCntOwner = col;
                }
                if (colTitle == "船名")
                {
                    indexVsl = col;
                }
                if (colTitle == "航次")
                {
                    indexVoyNumber = col;
                }
                if (colTitle == "装卸人手机")
                {
                    indexUnloadManMobileTel = col;
                }
                if (colTitle == "卸货人电话")
                {
                    indexUnloadManTel = col;
                }
                if (colTitle == "工作单号")
                {
                    indexOhBillNumber = col;
                }
                if (colTitle == "业务员")
                {
                    indexBizMan = col;
                }
                if (colTitle == "货主")
                {
                    indexGoodsOwner = col;
                }

                if (colTitle == "拖车公司")
                {
                    indexTruckCompany = col;
                }
                if (colTitle == "车牌号")
                {
                    indexTruckNumber = col;
                }
                if (colTitle == "司机名")
                {
                    indexDriver = col;
                }
                if (colTitle == "司机电话")
                {
                    indexDriverTel = col;
                }
                if (colTitle == "备注2")
                {
                    indexmeno = col;
                }
            }
           #endregion
            var data = new List<InUploadData>();

            for (int row = 1; row < rowCount; row++) 
            {

                var r = sheet.GetRow(row);
                if (r == null) continue;
                var cell =new object();
                if (indexOhBillNumber != -1)
                {
                     cell = r.GetCell(indexOhBillNumber);
                }
                if (cell==null||cell.ToString()=="")continue;

                // 导入车队和车号
                if (indexTruckCompany != -1 && indexTruckNumber != -1)
                {
                    var truckCompany = r.GetCell(indexTruckCompany).ToString();
                    var truckNumber = r.GetCell(indexTruckNumber).ToString();
                    var driver = indexDriver == -1 ? null : r.GetCell(indexDriver).ToString();
                    var driverTel = indexDriverTel == -1 ? null : r.GetCell(indexDriverTel).ToString();

                    new ImportService().ImportTruck(truckCompany, truckNumber, driver, driverTel);
                }

                var inOrOut = indexInOrOut == -1 ? string.Empty : r.GetCell(indexInOrOut).ToString();
                if (inOrOut != "拆箱")
                {
                    continue;
                }

                var up = new InUploadData();

                   up.BillNumber = indexOhBillNumber == -1 ? string.Empty : r.GetCell(indexOhBillNumber).ToString();
                   up.Vsl = indexVsl == -1 ? string.Empty : r.GetCell(indexVsl).ToString();
                   up. VoyNumber = indexVoyNumber == -1 ? string.Empty : r.GetCell(indexVoyNumber).ToString();
                   up. Clients = indexClients == -1 ? string.Empty : r.GetCell(indexClients).ToString();
                   up. ArrivalDT = indexArrivalDT == -1 ? string.Empty : ConverDT(r.GetCell(indexArrivalDT).ToString());
                   var sj = indexmeno == -1 ? string.Empty : r.GetCell(indexmeno).ToString();

                   #region 给卸货日期 加入时间 
                   string ss="",time="";
                   if (sj != "" && !sj.Contains("下午"))
                   {
                       if (sj.Contains(' '))
                       {
                           var indexs = sj.IndexOf(' ');
                           ss = sj.Substring(indexs + 1, sj.Length - (indexs + 1));
                       }
                       
                       if (!sj.Contains(":") && !sj.Contains("."))
                       {
                           if (ss != ""&&up.ArrivalDT!="")
                           {
                               time = (DateTime.Parse(up.ArrivalDT).AddHours(int.Parse(ss))).ToString();
                           }
                       }
                       else
                       {
                           var array = new string[2];
                           if (sj.Contains(":"))
                           {
                               array = ss.Split(':');
                              
                           }
                           else if(sj.Contains("."))
                           {
                               array = ss.Split('.');
                           }
                           if (up.ArrivalDT != "")
                           {
                               time = (DateTime.Parse(up.ArrivalDT).AddHours(int.Parse(array[0])).AddMinutes(double.Parse(array[1]))).ToString();

                           }
                       }
                      
                   }

                   if (time != "")
                   {
                       up.ArrivalDT = time;
                   }
                   #endregion

                   up.BizMan = indexBizMan == -1 ? string.Empty: r.GetCell(indexBizMan).ToString();
                   if (up.BizMan == "")
                   {
                       up.BizMan = UserService.GetLoginUser().Name;
                   }
                   up. BizDT = indexBizDT == -1 ? string.Empty : r.GetCell(indexBizDT).ToString();
                   up. BLNumber = indexBL == -1 ? string.Empty : r.GetCell(indexBL).ToString();
                   up. CntOwner = indexCntOwner == -1 ? string.Empty : r.GetCell(indexCntOwner).ToString();
                   up. CntNumber = indexCntNumber == -1 ? string.Empty : r.GetCell(indexCntNumber).ToString();
                   up. Address = indexAddress == -1 ? string.Empty : r.GetCell(indexAddress).ToString();
                   up. GoodsOwner = indexGoodsOwner == -1 ? string.Empty : r.GetCell(indexGoodsOwner).ToString();
                   up. Goods = indexGoods == -1 ? string.Empty : r.GetCell(indexGoods).ToString();
                   up. UnloadMan = indexUnloadMan == -1 ? string.Empty : r.GetCell(indexUnloadMan).ToString();
                   up. UnloadManTel = indexUnloadMan == -1 ? string.Empty : r.GetCell(indexUnloadManTel).ToString();
                   up.UnloadManMobileTel = indexUnloadManMobileTel == -1 ? string.Empty : r.GetCell(indexUnloadManMobileTel).ToString();
                   up.CntType20 = index20 == -1 ? string.Empty : r.GetCell(index20).NumericCellValue.ToString();
                   up.CntType40 = index40 == -1 ? string.Empty : r.GetCell(index40).NumericCellValue.ToString();
                   up.CntType40HQ = index40HQ == -1 ? string.Empty : r.GetCell(index40HQ).NumericCellValue.ToString();

                data.Add(up);
            }
         
            var icList = new List<InConsignation>();
            var dataIC = (from item in data
                         where !item.BillNumber.Contains("_")
                         orderby item.BillNumber
                          select item).ToList <InUploadData>();
            foreach (var ic in dataIC)
            {
                var c = new InConsignation
                {
                    BillNumber = ic.BillNumber,
                    Vsl = ic.Vsl,
                    VoyNumber = ic.VoyNumber,
                    Clients = ic.Clients,
                    DeliverDT = ic.ArrivalDT,
                    BizMan = ic.BizMan,
                    BizDT = ic.BizDT,
                    Items = new List<InConsignationDetail>()
                };
                icList.Add(c);
            }

            foreach (var ic in icList)
            {
                var dataICD = from item in data
                              where item.BillNumber.StartsWith(ic.BillNumber)
                              orderby item.BillNumber
                              select item;

                foreach (var icd in dataICD)
                {
                    var cd = new InConsignationDetail
                    {
                        BlNumber = icd.BLNumber,
                        CntOwner = icd.CntOwner,
                        Goods = icd.Goods,
                        GoodsOwner = icd.GoodsOwner,
                        UnloadMan = icd.UnloadMan,
                        Address = icd.Address
                    };
                    cd.UnloadManTel = icd.UnloadManTel;
                    if (!string.IsNullOrEmpty(icd.UnloadManMobileTel))
                    {
                        if (!string.IsNullOrEmpty(cd.UnloadManTel))
                        {
                            cd.UnloadManTel += " ";
                        }
                        cd.UnloadManTel += icd.UnloadManMobileTel;
                    }
                    string num1, seal1, num2, seal2;
                    SplitCntNumber(icd.CntNumber, out num1, out seal1, out num2, out seal2);
                    cd.CntNumber1 = num1;
                    cd.CntNumber2 = num2;
                    cd.CntSeal1 = seal1;
                    cd.CntSeal2 = seal2;
                    cd.CntNumber = this.CombineCntNumber(num1, seal1, num2, seal2);
                    cd.CntType = this.CalcCntType(icd.CntType20, icd.CntType40,
                        icd.CntType40HQ, icd.CntType40RF);
                    if (string.IsNullOrEmpty(icd.CntType20) || icd.CntType20 == "---")
                    {
                        cd.CntType1 = "40'";
                    }
                    else
                    {
                        cd.CntType1 = "20'";
                        if (!string.IsNullOrEmpty(cd.CntNumber2))
                        {
                            cd.CntType2 = "20'";
                        }
                    }
                    ic.Items.Add(cd);
                }
            }

            var userId = UserService.GetLoginUser().Id;

            WithTransaction(() =>
            {
                foreach (var ic in icList)
                {
                    // 检查委托是否已经导入过了
                    var sql = "select count(*) from tms_in_consignation where f_bill_num = @billNumber";
                    var p = CreateDbParameters();
                    p.AddWithValue("billNumber", ic.BillNumber);
                    var cnt = QueryCount(sql, p);
                    if (cnt > 0)
                    {
                        continue;
                    }

                    //导入基础数据
                    var importService = new ImportService();
                    importService.ImportVsl(ic.Vsl);
                    importService.ImportBizMan(ic.BizMan);
                    importService.ImportClients(ic.Clients);

                    var id = IdGeneratorService.NewId();

                    sql = @"insert into tms_in_consignation(id, f_bill_num, 
                                    f_vsl, f_voy_num, f_arrival_dt, f_clients, f_biz_man,
                                    f_biz_dt, create_user_id, create_datetime)
                                values (@id, @billNumber, @vsl, @voyNumber,
                                    @arrivalDT, @clients, @bizMan, @bizDT, @userId, getdate())";
                    p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("billNumber", ic.BillNumber);
                    p.AddWithValue("vsl", ic.Vsl);
                    p.AddWithValue("voyNumber", ic.VoyNumber);
                 //   var dt = ConverDT(ic.DeliverDT);
                    p.AddWithValue("arrivalDT", string.IsNullOrEmpty(ic.DeliverDT) ? null : ic.DeliverDT);
                    p.AddWithValue("clients", ic.Clients);
                    p.AddWithValue("bizMan", ic.BizMan);
                    var  dt = ConverDT(ic.BizDT);
                    p.AddWithValue("bizDT", string.IsNullOrEmpty(dt) ? null : dt);
                    p.AddWithValue("userId", userId);

                    Execute(sql, p);

                    foreach (var icd in ic.Items)
                    {
                        //导入基础数据
                        importService.ImportCntOwner(icd.CntOwner);
                        importService.ImportAddress(icd.Address);
                        importService.ImportCustomer(icd.GoodsOwner);
                        importService.ImportCntType(icd.CntType1);
                        importService.ImportCntType(icd.CntType2);

                        sql = @"insert into tms_in_consignation_detail(
                                    id, parent_id, f_cnt_owner, f_cnt_type,
                                    f_cnt_type_1, f_cnt_num_1, f_cnt_seal_1,
                                    f_cnt_type_2, f_cnt_num_2, f_cnt_seal_2,
                                    f_address, f_bl_number, f_cnt_num,
                                    f_goods_owner, f_goods, f_unload_man, f_unload_man_tel,
                                    create_user_id, create_datetime)
                                values(@id, @parentId, @cntOwner, @cntType,
                                        @cntType1, @cntNum1, @cntSeal1,
                                        @cntType2, @cntNum2, @cntSeal2,
                                        @address, @blNumber, @cntNumber,
                                        @goodsOwner, @goods, @unloadMan, @unloadManTel,
                                        @userId, getdate())";
                        p = CreateDbParameters();
                        p.AddWithValue("id", IdGeneratorService.NewId());
                        p.AddWithValue("parentId", id);
                        p.AddWithValue("cntOwner", icd.CntOwner);
                        p.AddWithValue("cntType", icd.CntType);
                        p.AddWithValue("cntType1", icd.CntType1);
                        p.AddWithValue("cntNum1", icd.CntNumber1);
                        p.AddWithValue("cntSeal1", icd.CntSeal1);
                        p.AddWithValue("cntType2", icd.CntType2);
                        p.AddWithValue("cntNum2", icd.CntNumber2);
                        p.AddWithValue("cntSeal2", icd.CntSeal2);
                        p.AddWithValue("cntNumber", icd.CntNumber);
                        p.AddWithValue("goodsOwner", icd.GoodsOwner);
                        p.AddWithValue("goods", icd.Goods);
                        p.AddWithValue("unloadMan", icd.UnloadMan);
                        p.AddWithValue("unloadManTel", icd.UnloadManTel);
                        p.AddWithValue("address", icd.Address);
                        p.AddWithValue("blNumber", icd.BlNumber);
                        p.AddWithValue("userId", userId);

                        Execute(sql, p);
                    }
                }
            });


            var result = new Result { Success = true };

            return JsonConvert.SerializeObject(result);
        }

        /// <summary>
        /// 把 "dd/mm/yyyy hh:mm"转换为 "yyyy-mm-dd hh:mm"
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private string ConverDT(string dt)
        {
            if (string.IsNullOrEmpty(dt))
            {
                return string.Empty;
            }

            var dd = string.Empty;
            var mm = string.Empty;
            var yyyy = string.Empty;
            var h = string.Empty;
            var m = string.Empty;

            var data = dt.Split(' ');
            if (data.Length == 1)
            {
                h = "00";
                m = "00";
            }
            else
            {
                var hmArray = data[1].Split(':');
                h = hmArray[0];
                m = hmArray[1];

                var ymdArray = data[0].Split('/');
                yyyy = ymdArray[2];
                mm = ymdArray[1];
                dd = ymdArray[0];
            }

            return yyyy + "-" + mm + "-" + dd + " " + h + ":" + m;
        }

        private string CalcCntType(string ct20, string ct40, string ct40qh, string ct40rf)
        {
            if (!string.IsNullOrEmpty(ct20) && ct20 != "0")
            {
                return "20'×" + ct20;
            }
            else
            {
                return "40'×1";
            }
        }

        private void SplitCntNumber(string cn, out string num1, out string seal1,
            out string num2, out string seal2)
        {
            num1 = string.Empty;
            seal1 = string.Empty;
            num2 = string.Empty;
            seal2 = string.Empty;
            if (string.IsNullOrEmpty(cn))
            {
                return;
            }

            var numArray = cn.Split('\r', '\n');
            if (numArray.Length == 1)
            {
                var num = numArray[0];
                var data = num.Split('/');
                if (data.Length == 1)
                {
                    num1 = data[0];
                }
                else
                {
                    num1 = data[0];
                    seal1 = data[1];
                }
            }
            else if (numArray.Length == 3)
            {
                var num = numArray[0];
                var data = num.Split('/');
                if (data.Length == 1)
                {
                    num1 = data[0];
                }
                else
                {
                    num1 = data[0];
                    seal1 = data[1];
                }

                num = numArray[2];
                data = num.Split('/');
                if (data.Length == 1)
                {
                    num2 = data[0];
                }
                else
                {
                    num2 = data[0];
                    seal2 = data[1];
                }

            }
        }

        public Result CancelPlan(string id)
        {

            //检查能否取消委托运输
            var sql = @"select count(*) from tms_in_consignation_detail 
                    where (parent_id = @id) and (f_status is not null) and (f_status <> '待下达计划')";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            var cnt = QueryCount(sql, p);
            if (cnt > 0)
            {
                return new Result { Success = false, Messgae = "送货明细已经下达计划，不能取消运输" };
            }

            WithTransaction(() =>
            {
                sql = @"
                        update tms_in_consignation_detail 
                        set f_status = null, f_plan_act_dt = null
                        where parent_id = @id

                        update tms_in_consignation 
                        set f_status = null, f_confirm_user_name = null, 
                            f_confirm_dt = null, f_plan_act_dt = null
                        where id = @id
                    ";
                p = CreateDbParameters();
                p.AddWithValue("id", id);
                Execute(sql, p);
            });

            return new Result { Success = true };
        }

        /// <summary>
        /// odb
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public string Odbupload(string fileName)
        {

            var ds = GetExcelSheetNames(fileName);

            var tableRowCount = ds.Tables[0].Rows.Count;
            var tableColumnsCount=ds.Tables[0].Columns.Count;

            #region 确认表头索引

            // 确认各列对应的数据
            var indexBizDT = -1;
            var indexClients = -1;
            var indexArrivalDT = -1;
            var indexBL = -1;
            var indexInOrOut = -1;
            var index20 = -1;
            var index40 = -1;
            var index40HQ = -1;
            var index40RF = -1;
            var indexGoods = -1;
            var indexCntNumber = -1;
            var indexAddress = -1;
            var indexUnloadMan = -1;
            var indexCntOwner = -1;
            var indexVsl = -1;
            var indexVoyNumber = -1;
            var indexUnloadManMobileTel = -1;
            var indexUnloadManTel = -1;
            var indexOhBillNumber = -1; //工作单号
            var indexBizMan = -1;
            var indexGoodsOwner = -1;

            var indexTruckCompany = -1;
            var indexTruckNumber = -1;
            var indexDriver = -1;
            var indexDriverTel = -1;



            for (int col = 0; col < tableColumnsCount; col++)
            {
                var cell = ds.Tables[0].Rows[0][col];
                if (cell == null) continue;

                var colTitle = cell.ToString();
                if (colTitle == "制单日期")
                {
                    indexBizDT = col;
                }
                if (colTitle == "托运人")
                {
                    indexClients = col;
                }
                if (colTitle == "装卸日期")
                {
                    indexArrivalDT = col;
                }
                if (colTitle == "运单号")
                {
                    indexBL = col;
                }
                if (colTitle == "装拆箱")
                {
                    indexInOrOut = col;
                }
                if (colTitle == "20'")
                {
                    index20 = col;
                }
                if (colTitle == "40'")
                {
                    index40 = col;
                }
                if (colTitle == "40HQ")
                {
                    index40HQ = col;
                }
                if (colTitle == "40RF")
                {
                    index40RF = col;
                }
                if (colTitle == "货名")
                {
                    indexGoods = col;
                }
                if (colTitle == "柜号")
                {
                    indexCntNumber = col;
                }
                if (colTitle == "卸货地点")
                {
                    indexAddress = col;
                }
                if (colTitle == "卸货联系人")
                {
                    indexUnloadMan = col;
                }
                if (colTitle == "船公司")
                {
                    indexCntOwner = col;
                }
                if (colTitle == "船名")
                {
                    indexVsl = col;
                }
                if (colTitle == "航次")
                {
                    indexVoyNumber = col;
                }
                if (colTitle == "装卸人手机")
                {
                    indexUnloadManMobileTel = col;
                }
                if (colTitle == "卸货人电话")
                {
                    indexUnloadManTel = col;
                }
                if (colTitle == "工作单号")
                {
                    indexOhBillNumber = col;
                }
                if (colTitle == "业务员")
                {
                    indexBizMan = col;
                }
                if (colTitle == "货主")
                {
                    indexGoodsOwner = col;
                }

                if (colTitle == "拖车公司")
                {
                    indexTruckCompany = col;
                }
                if (colTitle == "车牌号")
                {
                    indexTruckNumber = col;
                }
                if (colTitle == "司机名")
                {
                    indexDriver = col;
                }
                if (colTitle == "司机电话")
                {
                    indexDriverTel = col;
                }
            }

            #endregion

            #region 把数据装入集合
            var data = new List<InUploadData>();

            for (int row = 1; row < tableRowCount; row++)
            {

                var cell = ds.Tables[0].Rows[row][indexOhBillNumber];

                if (cell == null || cell.ToString() == "") continue;

                // 导入车队和车号
                if (indexTruckCompany != -1 && indexTruckNumber != -1)
                {
                    var truckCompany = ds.Tables[0].Rows[row][indexTruckCompany].ToString();
                    var truckNumber = ds.Tables[0].Rows[row][indexTruckNumber].ToString();
                    var driver = indexDriver == -1 ? null :ds.Tables[0].Rows[row][indexDriver].ToString();
                    var driverTel = indexDriverTel == -1 ? null : ds.Tables[0].Rows[row][indexDriverTel].ToString();

                    new ImportService().ImportTruck(truckCompany, truckNumber, driver, driverTel);
                }

                var inOrOut = indexInOrOut == -1 ? string.Empty : ds.Tables[0].Rows[row][indexInOrOut].ToString();
                if (inOrOut != "拆箱")
                {
                    continue;
                }

                var up = new InUploadData();

                up.BillNumber = indexOhBillNumber == -1 ? string.Empty : ds.Tables[0].Rows[row][indexOhBillNumber].ToString();
                up.Vsl = indexVsl == -1 ? string.Empty : ds.Tables[0].Rows[row][indexVsl].ToString();
                up.VoyNumber = indexVoyNumber == -1 ? string.Empty : ds.Tables[0].Rows[row][indexVoyNumber].ToString();
                up.Clients = indexClients == -1 ? string.Empty : ds.Tables[0].Rows[row][indexClients].ToString();
                up.ArrivalDT = indexArrivalDT == -1 ? string.Empty : ds.Tables[0].Rows[row][indexArrivalDT].ToString();
                up.BizMan = indexBizMan == -1 ? string.Empty : ds.Tables[0].Rows[row][indexBizMan].ToString();
                up.BizDT = indexBizDT == -1 ? string.Empty : ds.Tables[0].Rows[row][indexBizDT].ToString();
                up.BLNumber = indexBL == -1 ? string.Empty : ds.Tables[0].Rows[row][indexBL].ToString();
                up.CntOwner = indexCntOwner == -1 ? string.Empty : ds.Tables[0].Rows[row][indexCntOwner].ToString();
                up.CntNumber = indexCntNumber == -1 ? string.Empty : ds.Tables[0].Rows[row][indexCntNumber].ToString();
                up.Address = indexAddress == -1 ? string.Empty : ds.Tables[0].Rows[row][indexAddress].ToString();
                up.GoodsOwner = indexGoodsOwner == -1 ? string.Empty : ds.Tables[0].Rows[row][indexGoodsOwner].ToString();
                up.Goods = indexGoods == -1 ? string.Empty : ds.Tables[0].Rows[row][indexGoods].ToString();
                up.UnloadMan = indexUnloadMan == -1 ? string.Empty : ds.Tables[0].Rows[row][indexUnloadMan].ToString();
                up.UnloadManTel = indexUnloadMan == -1 ? string.Empty : ds.Tables[0].Rows[row][indexUnloadManTel].ToString();
                up.UnloadManMobileTel = indexUnloadManMobileTel == -1 ? string.Empty : ds.Tables[0].Rows[row][indexUnloadManMobileTel].ToString();
                up.CntType20 = index20 == -1 ? string.Empty : ds.Tables[0].Rows[row][index20].ToString();
                up.CntType40 = index40 == -1 ? string.Empty : ds.Tables[0].Rows[row][index40].ToString();
                up.CntType40HQ = index40HQ == -1 ? string.Empty : ds.Tables[0].Rows[row][index40HQ].ToString();

                data.Add(up);
            }
            #endregion

            #region 存入数据库

            var icList = new List<InConsignation>();
            var dataIC = (from item in data
                          where !item.BillNumber.Contains("_")
                          orderby item.BillNumber
                          select item).ToList<InUploadData>();
            foreach (var ic in dataIC)
            {
                var c = new InConsignation
                {
                    BillNumber = ic.BillNumber,
                    Vsl = ic.Vsl,
                    VoyNumber = ic.VoyNumber,
                    Clients = ic.Clients,
                    DeliverDT = ic.ArrivalDT,
                    BizMan = ic.BizMan,
                    BizDT = ic.BizDT,
                    Items = new List<InConsignationDetail>()
                };
                icList.Add(c);
            }

            foreach (var ic in icList)
            {
                var dataICD = from item in data
                              where item.BillNumber.StartsWith(ic.BillNumber)
                              orderby item.BillNumber
                              select item;

                foreach (var icd in dataICD)
                {
                    var cd = new InConsignationDetail
                    {
                        BlNumber = icd.BLNumber,
                        CntOwner = icd.CntOwner,
                        Goods = icd.Goods,
                        GoodsOwner = icd.GoodsOwner,
                        UnloadMan = icd.UnloadMan,
                        Address = icd.Address
                    };
                    cd.UnloadManTel = icd.UnloadManTel;
                    if (!string.IsNullOrEmpty(icd.UnloadManMobileTel))
                    {
                        if (!string.IsNullOrEmpty(cd.UnloadManTel))
                        {
                            cd.UnloadManTel += " ";
                        }
                        cd.UnloadManTel += icd.UnloadManMobileTel;
                    }
                    string num1, seal1, num2, seal2;
                    SplitCntNumber(icd.CntNumber, out num1, out seal1, out num2, out seal2);
                    cd.CntNumber1 = num1;
                    cd.CntNumber2 = num2;
                    cd.CntSeal1 = seal1;
                    cd.CntSeal2 = seal2;
                    cd.CntNumber = this.CombineCntNumber(num1, seal1, num2, seal2);
                    cd.CntType = this.CalcCntType(icd.CntType20, icd.CntType40,
                        icd.CntType40HQ, icd.CntType40RF);
                    if (string.IsNullOrEmpty(icd.CntType20) || icd.CntType20 == "---")
                    {
                        cd.CntType1 = "40'";
                    }
                    else
                    {
                        cd.CntType1 = "20'";
                        if (!string.IsNullOrEmpty(cd.CntNumber2))
                        {
                            cd.CntType2 = "20'";
                        }
                    }
                    ic.Items.Add(cd);
                }
            }

            var userId = UserService.GetLoginUser().Id;

            WithTransaction(() =>
            {
                foreach (var ic in icList)
                {
                    // 检查委托是否已经导入过了
                    var sql = "select count(*) from tms_in_consignation where f_bill_num = @billNumber";
                    var p = CreateDbParameters();
                    p.AddWithValue("billNumber", ic.BillNumber);
                    var cnt = QueryCount(sql, p);
                    if (cnt > 0)
                    {
                        continue;
                    }

                    //导入基础数据
                    var importService = new ImportService();
                    importService.ImportVsl(ic.Vsl);
                    importService.ImportBizMan(ic.BizMan);
                    importService.ImportClients(ic.Clients);

                    var id = IdGeneratorService.NewId();

                    sql = @"insert into tms_in_consignation(id, f_bill_num, 
                                    f_vsl, f_voy_num, f_arrival_dt, f_clients, f_biz_man,
                                    f_biz_dt, create_user_id, create_datetime)
                                values (@id, @billNumber, @vsl, @voyNumber,
                                    @arrivalDT, @clients, @bizMan, @bizDT, @userId, getdate())";
                    p = CreateDbParameters();
                    p.AddWithValue("id", id);
                    p.AddWithValue("billNumber", ic.BillNumber);
                    p.AddWithValue("vsl", ic.Vsl);
                    p.AddWithValue("voyNumber", ic.VoyNumber);
                    var dt = ConverDT(ic.DeliverDT);
                    p.AddWithValue("arrivalDT", string.IsNullOrEmpty(dt) ? null : dt);
                    p.AddWithValue("clients", ic.Clients);
                    p.AddWithValue("bizMan", ic.BizMan);
                    dt = ConverDT(ic.BizDT);
                    p.AddWithValue("bizDT", string.IsNullOrEmpty(dt) ? null : dt);
                    p.AddWithValue("userId", userId);

                    Execute(sql, p);

                    foreach (var icd in ic.Items)
                    {
                        //导入基础数据
                        importService.ImportCntOwner(icd.CntOwner);
                        importService.ImportAddress(icd.Address);
                        importService.ImportCustomer(icd.GoodsOwner);
                        importService.ImportCntType(icd.CntType1);
                        importService.ImportCntType(icd.CntType2);

                        sql = @"insert into tms_in_consignation_detail(
                                    id, parent_id, f_cnt_owner, f_cnt_type,
                                    f_cnt_type_1, f_cnt_num_1, f_cnt_seal_1,
                                    f_cnt_type_2, f_cnt_num_2, f_cnt_seal_2,
                                    f_address, f_bl_number, f_cnt_num,
                                    f_goods_owner, f_goods, f_unload_man, f_unload_man_tel,
                                    create_user_id, create_datetime)
                                values(@id, @parentId, @cntOwner, @cntType,
                                        @cntType1, @cntNum1, @cntSeal1,
                                        @cntType2, @cntNum2, @cntSeal2,
                                        @address, @blNumber, @cntNumber,
                                        @goodsOwner, @goods, @unloadMan, @unloadManTel,
                                        @userId, getdate())";
                        p = CreateDbParameters();
                        p.AddWithValue("id", IdGeneratorService.NewId());
                        p.AddWithValue("parentId", id);
                        p.AddWithValue("cntOwner", icd.CntOwner);
                        p.AddWithValue("cntType", icd.CntType);
                        p.AddWithValue("cntType1", icd.CntType1);
                        p.AddWithValue("cntNum1", icd.CntNumber1);
                        p.AddWithValue("cntSeal1", icd.CntSeal1);
                        p.AddWithValue("cntType2", icd.CntType2);
                        p.AddWithValue("cntNum2", icd.CntNumber2);
                        p.AddWithValue("cntSeal2", icd.CntSeal2);
                        p.AddWithValue("cntNumber", icd.CntNumber);
                        p.AddWithValue("goodsOwner", icd.GoodsOwner);
                        p.AddWithValue("goods", icd.Goods);
                        p.AddWithValue("unloadMan", icd.UnloadMan);
                        p.AddWithValue("unloadManTel", icd.UnloadManTel);
                        p.AddWithValue("address", icd.Address);
                        p.AddWithValue("blNumber", icd.BlNumber);
                        p.AddWithValue("userId", userId);

                        Execute(sql, p);
                    }
                }
            });

            #endregion

            var result = new Result { Success = true };

            return JsonConvert.SerializeObject(result);
        
        }

        public  DataSet GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            try
            {
                string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件 
                // string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 
                objConn = new OleDbConnection(strConn);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                List<string> cc = new List<string>();
                String[] excelSheets = new String[dt.Rows.Count/2];
                int i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    if (row["TABLE_NAME"].ToString().Contains("Print_Titles")) continue;
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                Array.Reverse(excelSheets);

                DataSet ds = new DataSet();
                for (int j = 0; j < excelSheets.Count(); j++)
                {
                   OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", excelSheets[j]), objConn);           //("select * from [Sheet1$]", conn); 
                   odda.Fill(ds, excelSheets[j]);  
                }


                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }


    }
}